package com.zhc.tools.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.collect.Maps;

/**
 * 说明：根据不同的文件类型读取excel
 * @author zhc
 */
public class ReadExcelByExtHelper {

	/**  
     * 自动解析根据传入的excel文件类型的方法，返回 TreeMap<Integer, Map<Integer, Object>> 对象
     * 	解析Excel文件，目前支持 xls、xlsx
     * @param file excel 文件类型
     * @return TreeMap<Integer, Map<Integer, Object>> 行序号，列序号->值
     * @throws IOException
     */
    public static TreeMap<Integer, Map<Integer, Object>> 
    					readExcelFromFile(File file) throws IOException {
    	if (file != null) {
    		String fileName = file.getName();
    		return readExcelFromInputStream(new FileInputStream(file), fileName);
    	}
    	return null;
    }
    
    /**
     * 自动解析根据传入的inputStream文件流的方法，返回 TreeMap<Integer, Map<Integer, Object>> 对象
     * 	解析Excel文件流，目前支持 xls、xlsx
     * @param inputStream 文件流
     * @param filename 文件名称（支持格式根据传入文件名后缀进行判断）
     * @return TreeMap<Integer, Map<Integer, Object>> 行序号，列序号->值
     * @throws IOException
     */
    public static TreeMap<Integer, Map<Integer, Object>> 
    				readExcelFromInputStream(InputStream inputStream, String filename) throws IOException {
    	if (inputStream != null && StringUtils.isNotBlank(filename)) {
    		String extension = filename.lastIndexOf(".") == -1 ? "" : filename.substring(filename.lastIndexOf(".") + 1);
    		if ("xls".equals(extension.toLowerCase())) {
    			return read2003Excel(inputStream);
    		} else if ("xlsx".equals(extension.toLowerCase())) {
    			return read2007Excel(inputStream);
    		} else {
    			throw new IOException("不支持的文件类型");  
    		}
    	}
    	return null;
    }
    
    /**  
     * 读取 office 2003 excel  
     *   
     * @throws IOException  
     * @throws FileNotFoundException  
     */  
    private static TreeMap<Integer, Map<Integer, Object>> read2003Excel(InputStream in)  
            throws IOException {  
    	TreeMap<Integer, Map<Integer, Object>> resultDataMap = Maps.newTreeMap();
        HSSFWorkbook hwb = new HSSFWorkbook(in);
        HSSFSheet sheet = hwb.getSheetAt(0);
        HSSFRow row = null;  
        HSSFCell cell = null;  
//        System.out.println("读取office 2003 excel内容如下：");  
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {  
            row = sheet.getRow(i);  
            if (row == null) {  
                continue;  
            }
            Map<Integer, Object> cellDataMap = Maps.newHashMap();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
            	Object value = null;  
                cell = row.getCell(j);  
                if (cell == null) {  
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    // System.out.println(i + "行" + j + " 列 is String type");  
                    value = cell.getStringCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
                    // System.out.println(i + "行" + j  
                    // + " 列 is Number type ; DateFormt:"  
                    // + cell.getCellStyle().getDataFormatString());  
                	String _dataFormatString = cell.getCellStyle().getDataFormatString();
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                        value = df.format(cell.getNumericCellValue());  
  
                    } else if ("General".equals(_dataFormatString)
		                			|| _dataFormatString.contains("0;")
		                			|| _dataFormatString.contains("0.000")
		                			|| _dataFormatString.contains("0.00")
		                			|| _dataFormatString.contains("0.0")
		                			|| _dataFormatString.contains("GENERAL")
		                			|| _dataFormatString.contains("0_")
		                			|| _dataFormatString.contains("0")
		            		) {
                        value = nf.format(cell.getNumericCellValue());  
                    } else {  
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell  
                                .getNumericCellValue()));  
                    }  
//                    System.out.print("  " + value + "  ");  
                    break;  
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    // System.out.println(i + "行" + j + " 列 is Boolean type");  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    // System.out.println(i + "行" + j + " 列 is Blank type");  
                    value = "";  
                    break;  
                default:  
                    // System.out.println(i + "行" + j + " 列 is default type");  
                    value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                    continue;  
                }  
                cellDataMap.put(j, value);
            }  
            resultDataMap.put(i, cellDataMap);
        }  
  
        return resultDataMap;  
    }
    
    /**  
     * 读取Office 2007 excel  
     */  
    private static TreeMap<Integer, Map<Integer, Object>> read2007Excel(InputStream in)  
            throws IOException {  
  
    	TreeMap<Integer, Map<Integer, Object>> resultDataMap = Maps.newTreeMap();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
        XSSFWorkbook xwb = new XSSFWorkbook(in);  
  
        // 读取第一章表格内容  
        XSSFSheet sheet = xwb.getSheetAt(0);  
        XSSFRow row = null;  
        XSSFCell cell = null;  
//        System.out.println("读取office 2007 excel内容如下：");  
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {  
            row = sheet.getRow(i);  
            if (row == null) {  
                continue;  
            }  
            Map<Integer, Object> cellDataMap = Maps.newHashMap();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {  
            	Object value = null;
                cell = row.getCell(j);  
                if (cell == null) {  
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String  
                // 字符  
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    // System.out.println(i + "行" + j + " 列 is String type");  
                    value = cell.getStringCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
                    // System.out.println(i + "行" + j  
                    // + " 列 is Number type ; DateFormt:"  
                    // + cell.getCellStyle().getDataFormatString());  
                	String _dataFormatString = cell.getCellStyle().getDataFormatString();
                    if ("@".equals(_dataFormatString)) {  
                        value = df.format(cell.getNumericCellValue());  
  
                    } else if ("General".equals(_dataFormatString)
	                    			|| _dataFormatString.contains("0;")
	                    			|| _dataFormatString.contains("0.000")
	                    			|| _dataFormatString.contains("0.00")
	                    			|| _dataFormatString.contains("0.0")
	                    			|| _dataFormatString.contains("GENERAL")
	                    			|| _dataFormatString.contains("0_")
	                    			|| _dataFormatString.contains("0")
                    		) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {  
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
                    }  
                    break;  
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    // System.out.println(i + "行" + j + " 列 is Boolean type");  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    // System.out.println(i + "行" + j + " 列 is Blank type");  
                    value = "";  
                    break;  
                default:  
                    // System.out.println(i + "行" + j + " 列 is default type");  
                    value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                    continue;  
                }  
                cellDataMap.put(j, value);
            }  
            resultDataMap.put(i, cellDataMap);
        }
        return resultDataMap;
    }
}
